%% Read excel
clc;
clear;
Path = 'D:\01_Coder\01_matlab\00_Code\01_qiandao\q签到';                   % 设置数据存放的文件夹路径
File = dir(fullfile(Path,'*.xlsx'));  % 显示文件夹下所有符合后缀名为.xlsx文件的完整信息
FileNames = {File.name}';            % 提取符合后缀名为.txt的所有文件的文件名，转换为n行1列
strName = ["Good"];
strstnum = ["Good"];
for i = 1:length(FileNames)
    file.name = [Path, '\', FileNames{i}];
    file.para = sheetnames(file.name);
    data.raw = readtable(file.name,'Sheet',file.para(1));
    student.ID{i} = data.raw(:,3);
    student.time{i} = table2cell(data.raw(1,4));
    if(size(data.raw,2)<6)
        tempstrName = NaN;
        strName(1:size(student.ID{i},1),i) = tempstrName;
        strstnum(1:size(student.ID{i}),i) = tempstrName;
    else
        tempstrName = string(table2cell(data.raw(:,5)));
        tempstrstnum = string(table2cell(data.raw(:,6)));
        strName(1:size(tempstrName,1),i) = tempstrName;
        strstnum(1:size(tempstrstnum,1),i) = tempstrstnum;
    end
    tempstr(i) = string(student.time{1,i});
    strlast(1) = ["wechat"];
    strlast(2) = ["name"];
    strlast(3) = ["student ID"];
    Varcell(1) = {'string'};
    Varcell(2) = {'string'};
    Varcell(3) = {'string'};
    strlast(i+3) = [tempstr(i)];
    Varcell(i+3) = {'double'}; 
    tempstrID = string(table2cell(student.ID{1,i}(:,:)));
    strID(1:size(tempstrID,1),i) = tempstrID;
end
TempstrID = strID;
TempstrID(:,[1 2 3 5 6]) = missing;
[allstudID,indxStu] = unique(TempstrID);
allstudID = rmmissing(allstudID);
allstudName = strName(indxStu);
allstudName  = rmmissing(allstudName);
allstudnum = strstnum(indxStu);
allstudnum  = rmmissing(allstudnum);
maxnum = size(allstudID,1);
T = table('Size',[maxnum length(FileNames)+3],'VariableTypes',Varcell,'VariableNames',strlast);
T(:,1) = table(allstudID);
for j = 1:maxnum
    for k = 1:length(FileNames)
        if(isempty(find(allstudID(j,1) == strID(:,k))))
            isqd(j,k) = 0;
        else
            isqd(j,k) = 1;
        end
    end
end
T(:,2) = splitvars(table(allstudName));
T(:,3) = splitvars(table(allstudnum));
T(:,4:end) = splitvars(table(isqd));
%% save 
fullname = 'qd1.xlsx';
% 4*(i-1)+6:4*i+5-(4-length(para.rawdata500.thermal.RCth.leg(1).S(1).tr.(loss_struct{i})))
writetable(T,fullname,'Sheet',"qd",'Range',"A1");


